#include "client_sq.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>


//将ptr按照separator分割
int split(char *ptr,char *separator,struct client_data *str)
{
	char     *p = ptr;
	char      a[3][50];	
	int       i,j=0;
	int       n = 0;
	int       length = strlen(p);

	if(!ptr || !separator || !str)
	{
		return -1;
	}

	memset(a,0,sizeof(a));
	for(i=0; i<3; i++)
	{
		for(j; j<length; j++)
		{
			if((*separator!= p[j])&&('\0'!=p[j]))
			{
				a[i][n]= p[j];
				n++;
				continue;
			}
			n = 0;
			j++;
			break;
		}
	}

	strcpy(str->id,a[0]);      	
	str->temp = atof(a[1]);
	strcpy(str->time,a[2]);
	return 0;

}		
			
//在数据库DB下创建表table			
sqlite3 *create_database()
{
	sqlite3 *db;
	char          *zErrMsg = 0;
	int            rc;
	char           sql[1024];

	rc = sqlite3_open("client.db",&db);
	if(rc)
	{
		printf("Can not open database:%s\n",strerror(errno));
		return NULL;
	}

	memset(sql,0,sizeof(sql));
	sprintf(sql,"CREATE TABLE IF NOT EXISTS CLIENT_TEMP("\
              "ID INTEGER PRIMARY KEY AUTOINCREMENT    NOT NULL,"\
              "CLIENT_ID          CHAR(10)             NOT NULL,"\
              "TEMP               REAL                 NOT NULL,"\
              "TIME               CHAR(50)             NOT NULL);");

	rc = sqlite3_exec(db,sql,NULL,0,&zErrMsg);
	if(rc != SQLITE_OK)
	{
		printf("SQL error:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return NULL;
	}
	printf("Table created successfully\n");
	return db;
}



//将p(编号-温度-日期)插入表table
int insert_temp(char *p,sqlite3 *db)
{     		
	char                   *zErrMsg = 0;     	
	char                    sql[100];
	struct client_data      str;
	int                     rc;

	if(!p || !db)
	{
		return -1;
	}

	memset(&str,0,sizeof(str));

	//分割
	if(split(p,SEPARATOR,&str) < 0)
	{
		printf("splite failure:%s\n",strerror(errno));
		return -2;
	}
        
	memset(sql,0,sizeof(sql));      	
	sprintf(sql,"INSERT INTO CLIENT_TEMP (CLIENT_ID,TEMP,TIME) VALUES('%s','%f','%s');",str.id,str.temp,str.time);

        /* Execute SQL statement*/      	
	rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
	if(rc != SQLITE_OK)
	{
		printf("SQL error:%s\n",zErrMsg);
		sqlite3_free(zErrMsg);
		return -3;
	}
	printf("Back-up data to table CLIENT_TEMP\n");
	printf("\n");
 
      	return 0;
}

//从table中删除id
int delete_temp(int id,sqlite3 *db)
{
	char              *zErrMsg = 0;       	
	int                rc; 	
	char               sql[100];

	if(id < 0 || !db)
	{
		return -1;
	}

	memset(sql,0,sizeof(sql));      	
	sprintf(sql,"DELETE FROM CLIENT_TEMP WHERE ID=%d;",id);

	/* Execute SQL statement */
	rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
	if( rc != SQLITE_OK )
	{
		printf("SQL error: %s\n", zErrMsg); 
		sqlite3_free(zErrMsg); 
		return -1;
	}   
	//printf("Delete data successfully\n");
	return 0;
}

//查询table中的第一条记录buf，并返回其编号,没有记录返回-1
int selfirst_temp(char *buf,sqlite3 *db)
{
	char              *zErrMsg = 0;
	char               sql[1024];
	char             **dbResult;
	int                nRow;    //查询得到的记录数  此次只能查询一条语句
	int                nCol;    //列数
	int                id;      //编号
	int                i;
	int                n = 3;   //3条
	int                rc = -1;

	if(!buf || !db)
	{
		return -1;
	}
	memset(sql,0,sizeof(sql)); 
	sprintf(sql,"SELECT * FROM CLIENT_TEMP LIMIT 1;");

	/* Execute SQL statement */
	rc = sqlite3_get_table(db,sql,&dbResult,&nRow,&nCol,&zErrMsg);
	if(nRow == 0)   //没有查询到记录
	{
		return -1;
	}	       
	if( rc != SQLITE_OK )
	{      
		printf("SQL error: %s\n", zErrMsg);     
		sqlite3_free(zErrMsg);
	}
	//printf("Select first data from the table\n");

	i = nCol+1;  //5
	n += i;      //8
	for(i; i<n; i++)
	{
		strcat(buf,dbResult[i]);
		//不是最后一个
		if(i != (n-1))
		{
			//中间加上分割符
	        	strcat(buf,SEPARATOR);
		}
	}

	id = atoi(dbResult[4]);
	sqlite3_free_table(dbResult);

	return id;
}
